home *** CD-ROM | disk | FTP | other *** search
using System; using System.Collections; using System.Data.Common; using System.Web; using GBPVR.Public; using GBPVRSchedule; /// <summary> /// Summary description for Search /// </summary> /// namespace gbweb.classes { public class SearchEngine : IDisposable { //Contains the list of programmes that are found to match the users search criteria private ArrayList ProgrammeArray = new ArrayList(); //Reader used to access the database private DbDataReader aReader; //Used to pull programmes based off of passed in date and time filter values private DateTime startListingTime = new DateTime(); private DateTime endListingTime = new DateTime(); //Holds passed in search date and time filter values private string startdate = string.Empty; private string starttime = string.Empty; private string enddate = string.Empty; private string endtime = string.Empty; //Intialize the working search dates to dummy values DateTime startDate = Convert.ToDateTime("01/01/1901"); DateTime startTime = Convert.ToDateTime("01:01 AM"); DateTime endDate = Convert.ToDateTime("01/01/1901"); DateTime endTime = Convert.ToDateTime("01:01 AM"); public SearchEngine() { // // TODO: Add constructor logic here // } #region IDisposable Members public void Dispose() { ProgrammeArray.Clear(); } #endregion //This method initializes and sets the search dates that are needed to process and filter the search public void setSearchDateTime(string searchStartDate, string searchStartTime, string searchEndDate, string searchEndTime) { startdate = searchStartDate; starttime = searchStartTime; enddate = searchEndDate; endtime = searchEndTime; //Set the working search dates from the passed in string based values if (startdate != "01/01/1901") { if (starttime != "01:01 AM") { startDate = Convert.ToDateTime(startdate); startTime = Convert.ToDateTime(starttime); } else { startDate = Convert.ToDateTime(startdate); } } else { if (starttime != "01:01 AM") { startTime = Convert.ToDateTime(starttime); } } if (enddate != "01/01/1901") { if (endtime != "01:01 AM") { endDate = Convert.ToDateTime(enddate); endTime = Convert.ToDateTime(endtime); } else { endDate = Convert.ToDateTime(enddate); } } else { if (endtime != "01:01 AM") { endTime = Convert.ToDateTime(endtime); } } //Initialize the search base date span startListingTime = DateTime.Now; endListingTime = startListingTime.AddDays(30); //This is the code to that will update the startListingTime and endListingTime to contain search base start/end date values for pulling recordings if (startDate.Year != 1901) { if (startTime.ToString("HH:mm") != "01:01") { startListingTime = startDate.Date + startTime.TimeOfDay; } else { DateTime tmp = DateTime.Now.Date; startListingTime = startDate.Date + tmp.TimeOfDay; } } else { if (startTime.ToString("HH:mm") != "01:01") { startListingTime = DateTime.Now.Date + startTime.TimeOfDay; } else { startListingTime = DateTime.Now; } } if (endDate.Year != 1901) { if (endTime.ToString("HH:mm") != "01:01") { endListingTime = endDate.Date + endTime.TimeOfDay; } else { DateTime tmp = DateTime.Now.Date; endListingTime = endDate.Date + tmp.AddHours(23).AddMinutes(59).TimeOfDay; } } else { if (endTime.ToString("HH:mm") != "01:01") { endListingTime = DateTime.Now.AddDays(30).Date + endTime.TimeOfDay; } else { DateTime tmp = DateTime.Now.Date; endListingTime = DateTime.Now.AddDays(30).Date + tmp.AddHours(23).AddMinutes(59).TimeOfDay; } } int CmprDt = DateTime.Compare(startListingTime, endListingTime); //Check to see if the user entered a start date that is greater than the end date and if so set the start date to be 30 days prior to the end date if (CmprDt > 0) { startListingTime = endListingTime.AddDays(-30); } } //This method is where programmes are pulled from the database using a query based on passed in user criteria. //User filters that can not be applied via the query process are provided via the filterSerch method wich is called prior to this //method returning the arraylist of programmes to the calling class. public ArrayList SelectProgrammes(Array channels, Array selectedGenre, bool checkTitle, bool checkDesc, bool checkSubtitle, bool matchTitle, bool matchDesc, bool matchSubtitle, bool matchUniqueID, String searchFor, bool caseSensitive, Schedule scheduleHelper, int programmeMinLength, int programmeMaxLength) { //Instantiate the arraylist for holding programmes ProgrammeArray = new ArrayList(); //If nothing was entered to search for then we want to defualt the search flags to be false if (searchFor == null || searchFor.Length < 1) { checkTitle = false; matchTitle = false; checkSubtitle = false; matchSubtitle = false; checkDesc = false; matchDesc = false; } //For searches passed in from the detail screen we have to URL Decode it back to normal and then replace and 's with ''s if (searchFor != null) { searchFor = HttpUtility.UrlDecode(searchFor).Replace("'", "''"); } //Instantiate variables used in the creation of the search SQL command string chnl_cmd; bool chnl_filter; string chnl_join; string genre_cmd; bool genre_filter; string genre_join; string pgm_cmd; string where_cmd; initializeSQLCommands(channels, out chnl_cmd, out chnl_filter, out chnl_join, out genre_cmd, out genre_filter, out genre_join, out pgm_cmd, selectedGenre, out where_cmd); //Only go through this processing if wer are not matching the programme unique id if (!matchUniqueID) { //Check to see if we are searching the Title or matching on the Title if (checkTitle || matchTitle) { //Check to see if we are searching the title if (checkTitle) { where_cmd += " and UPPER(PROGRAMME.name) like '%" + searchFor + "%'"; } else //We are matching the title { where_cmd += " and UPPER(PROGRAMME.name) = '" + searchFor + "'"; } //Process and then execute the query DbConnection aConnection = processQuery(chnl_cmd, chnl_join, genre_cmd, genre_join, ref pgm_cmd, ref where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { Programme pgm = scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0)); if (!caseSensitive) { ProgrammeArray.Add(scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0))); } else { if (pgm.getTitle().IndexOf(searchFor) > -1) { ProgrammeArray.Add(pgm); } } } //Close the reader and connection closeReader(aConnection); //Re-Initialize SQL Commands in case the user has choosen to pull programmes based on other criteria such as sub-title or description initializeSQLCommands(channels, out chnl_cmd, out chnl_filter, out chnl_join, out genre_cmd, out genre_filter, out genre_join, out pgm_cmd, selectedGenre, out where_cmd); } //Check to see if we are searching the Subtitle or matching on the Subtitle if (checkSubtitle || matchSubtitle) { //Check to see if we are searching the subtitle if (checkSubtitle) { where_cmd += " and UPPER(PROGRAMME.sub_title) like '%" + searchFor + "%'"; } else //We are matching the title { where_cmd += " and UPPER(PROGRAMME.sub_title) = '" + searchFor + "'"; } //Process and then execute the query DbConnection aConnection = processQuery(chnl_cmd, chnl_join, genre_cmd, genre_join, ref pgm_cmd, ref where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { Programme pgm = scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0)); if (!caseSensitive) { if (!ProgrammeArray.Contains(pgm)) { ProgrammeArray.Add(pgm); } } else { if (pgm.getSubTitle().IndexOf(searchFor) > -1) { if (!ProgrammeArray.Contains(pgm)) { ProgrammeArray.Add(pgm); } } } } //Close the reader and connection closeReader(aConnection); //Re-Initialize SQL Commands in case the user has choosen to pull programmes based on other criteria such as sub-title or description initializeSQLCommands(channels, out chnl_cmd, out chnl_filter, out chnl_join, out genre_cmd, out genre_filter, out genre_join, out pgm_cmd, selectedGenre, out where_cmd); } //Check to see if we are searching the Dexcription or matching on the Description if (checkDesc || matchDesc) { //Check to see if we are searching the subtitle if (checkSubtitle) { where_cmd += " and UPPER(PROGRAMME.description) like '%" + searchFor + "%'"; } else //We are matching the title { where_cmd += " and UPPER(PROGRAMME.description) = '" + searchFor + "'"; } //Process and then execute the query DbConnection aConnection = processQuery(chnl_cmd, chnl_join, genre_cmd, genre_join, ref pgm_cmd, ref where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { Programme pgm = scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0)); if (!caseSensitive) { if (!ProgrammeArray.Contains(pgm)) { ProgrammeArray.Add(pgm); } } else { if (pgm.getDescription().IndexOf(searchFor) > -1) { if (!ProgrammeArray.Contains(pgm)) { ProgrammeArray.Add(pgm); } } } } //Close the reader and connection closeReader(aConnection); //Re-Initialize SQL Commands in case the user has choosen to pull programmes based on other criteria such as sub-title or description initializeSQLCommands(channels, out chnl_cmd, out chnl_filter, out chnl_join, out genre_cmd, out genre_filter, out genre_join, out pgm_cmd, selectedGenre, out where_cmd); } } else //We are searching for a unique programme via the unique programme identifier { ProgrammeArray.Clear(); where_cmd += " and unique_identifier = '" + searchFor + "'"; DbConnection aConnection = executeQuery(pgm_cmd, where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { ProgrammeArray.Add(scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0))); } closeReader(aConnection); } //If the user has not selected to reduce the search by using something in the title, sub-title or description we need to load up all found //programmes in the timeperiod. if (checkTitle || checkSubtitle || checkDesc || matchTitle || matchDesc || matchSubtitle || matchUniqueID) { //Do nothing since we have narrowed the returned programmes to something we were searching on from the Programme table } else { //Since the user didn't filter on any text check to see if they are filtering on the Channel or Genre if (chnl_filter || genre_filter) { //We are filtering on both the Channel and Genre if (chnl_filter && genre_filter) { pgm_cmd += " " + chnl_join; where_cmd += " and " + chnl_cmd; pgm_cmd += " " + genre_join; where_cmd += " and " + genre_cmd; DbConnection aConnection = executeQuery(pgm_cmd, where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { ProgrammeArray.Add(scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0))); } closeReader(aConnection); } else { //We are filtering on just the Channel if (chnl_filter) { pgm_cmd += " " + chnl_join; where_cmd += " and " + chnl_cmd; DbConnection aConnection = executeQuery(pgm_cmd, where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { ProgrammeArray.Add(scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0))); } closeReader(aConnection); } else //We are filtering on just the Genre { pgm_cmd += " " + genre_join; where_cmd += " and " + genre_cmd; DbConnection aConnection = executeQuery(pgm_cmd, where_cmd); // iterate through the results to load found programme(s)to the array while (aReader.Read()) { ProgrammeArray.Add(scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0))); } closeReader(aConnection); } } } else //The user is only filtering on the date and or time so load up all progammes that fall into the date/time range { DbConnection aConnection = executeQuery(pgm_cmd, where_cmd); while (aReader.Read()) { ProgrammeArray.Add(scheduleHelper.GetProgrammeByOID(aReader.GetInt32(0))); } closeReader(aConnection); } } //We need to check to see if there were additional search filtering that needs to be applied to the results that could not be applied //within the query itself filterSearchResults(programmeMinLength, programmeMaxLength); //Return the Array that contains the programmes that matched all the search critera the user entered return ProgrammeArray; } //This method is used to initialize the base SQL commands used in the search query command private void initializeSQLCommands(Array channels, out string chnl_cmd, out bool chnl_filter, out string chnl_join, out string genre_cmd, out bool genre_filter, out string genre_join, out string pgm_cmd, Array selectedGenre, out string where_cmd) { //Instantiate the bare minimum SQL command lines that will be used in the query pgm_cmd = "SELECT PROGRAMME.oid FROM PROGRAMME"; where_cmd = " where PROGRAMME.start_time >= datetime('" + startListingTime.ToString("yyyy-MM-ddTHH:mm:ss") + "') and PROGRAMME.end_time <= datetime('" + endListingTime.ToString("yyyy-MM-ddTHH:mm:ss") + "')"; //Instantiate the variables used in building the extended refinement SQL command strings //Build the static refinement SQL command strings build_Refinement_Query_Commands(channels, out chnl_cmd, out chnl_filter, out chnl_join, out genre_cmd, out genre_filter, out genre_join, selectedGenre); } //This method is used to create the extended parts of the query command that may be used to refine the search query SQL private static void build_Refinement_Query_Commands(Array channels, out string chnl_cmd, out bool chnl_filter, out string chnl_join, out string genre_cmd, out bool genre_filter, out string genre_join, Array selectedGenre) { chnl_cmd = string.Empty; chnl_filter = false; chnl_join = "LEFT JOIN CHANNEL ON PROGRAMME.channel_oid = CHANNEL.oid"; genre_cmd = string.Empty; genre_filter = false; genre_join = "LEFT JOIN PROGRAMME_GENRE ON PROGRAMME.oid = PROGRAMME_GENRE.programme_oid LEFT JOIN GENRE ON GENRE.oid = PROGRAMME_GENRE.genre_oid"; //Build the command for filtering by Channel if the user selected any channels to filter on if (channels != null && channels.Length > 0) { chnl_cmd = "CHANNEL.channel_number IN("; chnl_filter = true; foreach (string chnl in channels) { chnl_cmd += Convert.ToInt32(chnl) + ","; } int idx = chnl_cmd.LastIndexOf(","); chnl_cmd = chnl_cmd.Remove(idx); chnl_cmd += ")"; } //Build the command for filtering by Genre if the user selected any genre to filter on if (selectedGenre != null && selectedGenre.Length > 0) { genre_cmd = "GENRE.genre_name IN("; genre_filter = true; foreach (string genre in selectedGenre) { genre_cmd += "'" + genre + "',"; } int idx = genre_cmd.LastIndexOf(","); genre_cmd = genre_cmd.Remove(idx); genre_cmd += ")"; } } //This method is used to generate the final query command and to execute the query private DbConnection processQuery(string chnl_cmd, string chnl_join, string genre_cmd, string genre_join, ref string pgm_cmd, ref string where_cmd) { pgm_cmd = refineSearchQuery(chnl_cmd, chnl_join, genre_cmd, genre_join, pgm_cmd, ref where_cmd); //Execute the completed query return executeQuery(pgm_cmd, where_cmd); } //This method is where we may add more parameters to the query string to help further refine the search query private static string refineSearchQuery(string chnl_cmd, string chnl_join, string genre_cmd, string genre_join, string pgm_cmd, ref string where_cmd) { //Check to see if we are refining the searh by Channel or Genre if (chnl_cmd != string.Empty || genre_cmd != string.Empty) { if (chnl_cmd != string.Empty && genre_cmd != string.Empty) //We are refining the search by both Channel and Genre { pgm_cmd += " " + chnl_join; where_cmd += " and " + chnl_cmd; pgm_cmd += " " + genre_join; where_cmd += " and " + genre_cmd; } else //We are only refining the search by either the Channel or Genre { if (chnl_cmd != string.Empty) //We are refining the search just by Channel { pgm_cmd += " " + chnl_join; where_cmd += " and " + chnl_cmd; } else //We are refining the search just by Genre { pgm_cmd += " " + genre_join; where_cmd += " and " + genre_cmd; } } } return pgm_cmd; } //This method is where any filtering that can not be done via the raw query should be performed. //The method clears and reloads the arraylist of programmes that were found with the query to only contain programmes that still meet the //search criteria following the check of these non-query based filter parameters private void filterSearchResults(int minLength, int maxLength) { //We only need to do this processing for things that have not already been taken care of by the parameters applied in the queries. //Therefore if we are not using any of these additional search filters there is no sense in adding the extra overhead with additional //processing. To do this we check all filter parameters that are to be checked in this method to see if they are equal to their default values. if (!((minLength == int.MinValue) && (maxLength == int.MaxValue) && (startdate == "01/01/1901") && (enddate == "01/01/1901") && (starttime == "01:01 AM") && (endtime == "01:01 AM"))) { //Instantiate a new temporary arraylist to process the remaiing filter criteria ArrayList pgmArray = new ArrayList(ProgrammeArray); //Clear the arralist of found programmes since we will be reloading with programmes that meet any filtering we need to apply ProgrammeArray.Clear(); //Set the checksum of the start and end dates that is used in the filter process int parmStartDateSum = startDate.Month + startDate.Day + startDate.Year; int parmEndDateSum = endDate.Month + endDate.Day + endDate.Year; //Date processing logic to compare programmes that have already matched a entered search parameter foreach (Programme program in pgmArray) { bool showProgram = true; int pgmStartDateSum = program.getStartTime().Month + program.getStartTime().Day + program.getStartTime().Year; int pgmEndDateSum = program.getEndTime().Month + program.getEndTime().Day + program.getEndTime().Year; double length = (program.getEndTime() - program.getStartTime()).TotalMinutes; showProgram &= (length >= minLength) && (length <= maxLength); if (showProgram) { //Check to see if the passed in start date is a dummy date if (parmStartDateSum != 1903) { if (pgmStartDateSum < parmStartDateSum) { showProgram = false; } } //Check to see if the passed in start time is a dummy time if (showProgram && startTime.ToString("HH:mm") != "01:01") { int Cmpr = String.Compare(program.getStartTime().ToString("HH:mm"), startTime.ToString("HH:mm")); if (Cmpr < 0) { showProgram = false; } } //Check to see if the passed in end date is a dummy date if (showProgram && parmEndDateSum != 1903) { if (pgmEndDateSum > parmEndDateSum) { showProgram = false; } } //Check to see if the passed in end time is a dummy time if (showProgram && endTime.ToString("HH:mm") != "01:01") { int Cmpr = String.Compare(program.getEndTime().ToString("HH:mm"), endTime.ToString("HH:mm")); if (Cmpr > 0) { showProgram = false; } } } if (showProgram) { ProgrammeArray.Add(program); } } //Empty the temporary programme array since we are done with it pgmArray.Clear(); } } //This method creates the connection and executes the query private DbConnection executeQuery(string pgm_cmd, string where_cmd) { // create the database connection DbConnection aConnection = Global.GetOpenGBPVRDbConnection(); // create the command object and store the sql query DbCommand aCommand = aConnection.CreateCommand(); //Create the command sql query string aCommand.CommandText = pgm_cmd + where_cmd; aCommand.Connection = aConnection; // create the datareader object to connect to table and execute the query aReader = aCommand.ExecuteReader(); return aConnection; } //This method closes the reader that was being used as well as the connection that was being used private void closeReader(DbConnection aConnection) { // close the reader aReader.Close(); //close the connection aConnection.Close(); } } } 
